package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Purmi;
import com.clouds.wms.bean.Purmj;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;
import com.clouds.wms.util.SysParams;

@Repository("purmiDao")
public class PurmiDaoImpl implements PurmiDao {
	private Log log = LogFactory.getLog(getClass());
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Override
	public PagesBean getPurmiList(String miaa,String miab,String startMiac,String endMiac,String miad, int offset, int pageSize)throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Purmi> list = new ArrayList<Purmi>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT * FROM purmi ");
		if(miaa != null && !"".equals(miaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miaa = '" + Function.formatSQLString(miaa) + "'");
		}
		if(miad != null && !"".equals(miad)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miad = '" + Function.formatSQLString(miad) + "'");
		}
		if(miab != null && !"".equals(miab)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miab <> '" + SysParams.AUDIT_YARDS_COMPLETE + "'");
		}else{
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miab = '" + SysParams.AUDIT_YARDS_COMPLETE + "' ");
		}
		if(startMiac != null && !"".equals(startMiac)) {
			sql.append( " maac >= '" + Function.formatSQLString(startMiac) + "' ");
		}
		if(endMiac != null && !"".equals(endMiac)) {
			sql.append(" maac <= '" + Function.formatSQLString(endMiac) + "' ");
		}
		sql.append(" ORDER BY miaa DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Purmi>(Purmi.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public PagesBean getPurmjList(String mjaa, int offset, int pageSize)throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Purmj> list = new ArrayList<Purmj>();
		StringBuffer sql = new StringBuffer("SELECT * FROM purmj" );
		sql.append(" WHERE mjaa = '"+mjaa+"'");
		sql.append(" ORDER BY mjaa DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Purmj>(Purmj.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public Purmi getPurmi(String miaa) throws DatabaseException {
		String sql = "SELECT * FROM purmi WHERE miaa=? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{miaa}, new BeanPropertyRowMapper<Purmi>(Purmi.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int delPurmi(String miaa) throws DatabaseException {
		String sql = "delete from purmi where miaa = ? ";
		return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{miaa});
	}
	@Override
	public int delPurmj(String miaa) throws DatabaseException {
		String sql = "delete from purmj where mjaa = ? ";
		return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{miaa});
	}
	@Override
	public int[] savePurmjList(List<Purmj> purmiList) throws DatabaseException {
		String sql = "INSERT INTO purmj (mjaa,mjab,mjac,mjad,mjae,mjaf,mjag,mjah,mjai,mjaj,mjak,mjal,mjam,mjan,mjao,mjap,mjaq) VALUES (:mjaa,:mjab,:mjac,:mjad,:mjae,:mjaf,:mjag,:mjah,:mjai,:mjaj,:mjak,:mjal,:mjam,:mjan,:mjao,:mjap,:mjaq)";
		try {
			return namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(purmiList.toArray()));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int savePurmi(Purmi purmi) throws DatabaseException {
		String sql = "INSERT INTO purmi (miaa,miab,miac,miad,miae) VALUES (:miaa,:miab,:miac,:miad,:miae)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(purmi);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public Purmi getPurmiBySql(String sql) throws DatabaseException {
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new BeanPropertyRowMapper<Purmi>(Purmi.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
}
